/*  This file is a part of JJSched source code
    JJSched - Java Jabber Scheduler - a simple Jabber scheduling application
    Copyright (C) 2008 Cestmir "CZestmyr" Houska

    This program is free software: you can redistribute it and/or modify
    it under the terms of the GNU General Public License as published by
    the Free Software Foundation, either version 3 of the License, or
    (at your option) any later version.

    This program is distributed in the hope that it will be useful,
    but WITHOUT ANY WARRANTY; without even the implied warranty of
    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
    GNU General Public License for more details.

    You should have received a copy of the GNU General Public License
    along with this program.  If not, see <http://www.gnu.org/licenses/> */

package czestmyr.jjsched;

import java.util.*;
import java.util.concurrent.locks.*;
import java.sql.*;
import java.text.*;

import java.nio.charset.*;

/**
 * Provides the connection to the database server.
 * This class performs only basic integrity checks and prevents for example adding of users.
 * that are not registered to JJSched or setting privileges to illegal values.
 * But it does not check whether the user has privileges to do an action.
 */
public class DatabaseConnector{
    DatabaseConnector(Config c, TimeHandler newTime){
        conf = c;
        timeHandler = newTime;
        userCache = new HashMap<String, UserData>();
        adminCache = new HashMap<String, Integer>();
    }

    /**
     * Tries to connect to the database as specified in the config file.
     * 
     * @param   autocreate  Whether the connector should automatically create new DB tables if they are not present
     *                      or broken
     * @throws  Exception   If the connection could not be created or established or in case of database inconsistency.
     */
    public void connect(boolean autocreate) throws Exception{
        //Load the mysql DB first
        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            throw new Exception("The jdbc mysql driver could not be found\nCheck, whether you have jdbc installed.\nOriginal exception:\n"+e.getMessage());
        }

        //Get sql connection parameters from the config file
        String host, port, scheme, user, pass;
        host = conf.getString("MySQLHost");
        port = conf.getString("MySQLPort");
        scheme = conf.getString("MySQLScheme");
        user = conf.getString("MySQLUsername");
        pass = conf.getString("MySQLPassword");
        if (host == null) throw new Exception("Config error: specify 'MySQLHost' option in config file!");
        if (port == null) throw new Exception("Config error: specify 'MySQLPort' option in config file!");
        if (scheme == null) throw new Exception("Config error: specify 'MySQLScheme' option in config file!");
        if (user == null) throw new Exception("Config error: specify 'MySQLUsername' option in config file!");
        if (pass == null) throw new Exception("Config error: specify 'MySQLPassword' option in config file!");
        String url = "jdbc:mysql://"+host+":"+port+"/"+scheme+"?characterEncoding=utf8";
        conn = DriverManager.getConnection(url, user, pass);
        statement = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
                                         ResultSet.CONCUR_READ_ONLY);
		resultStatement = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
											   ResultSet.CONCUR_READ_ONLY);

        try {
            checkDB();
        } catch (Exception e) {
            //If autocreate option is not specified, throw an exception. Else continue and reset the DB
            if (!autocreate)
                throw new Exception(e.getMessage()+"\n    Note: If you don't want this error, launch jjsched with option"+
                                                   " --autocreate, but beware - this option DROPS all interfering"+
                                                   " tables and replaces them with the correct tables for jjsched");
        }

        if (autocreate) try {
            createTables();
        } catch (Exception e) {
            throw new Exception("Error while creating new tables:\n"+e.getMessage());
        }

        mirrorCaches();
    }

	/** Locks the data read lock */
	private void lr() {
		dataLock.readLock().lock();
	}

	/** Locks the data write lock */
	private void lw() {
		dataLock.writeLock().lock();
	}

	/** Unlocks the data read lock */
	private void ur() {
		dataLock.readLock().unlock();
	}

	/** Unlocks the data write lock */
	private void uw() {
		dataLock.writeLock().unlock();
	}

	/** Locks the data read lock and result lock*/
	private void lrr() {
		resultLock.lock();
		dataLock.readLock().lock();
	}

	/** Locks the data write lock and result lock*/
	private void lwr() {
		resultLock.lock();
		dataLock.writeLock().lock();
	}

	/** Unlocks the data read lock and result lock*/
	private void urr() {
		dataLock.readLock().unlock();
		resultLock.unlock();
	}

	/** Unlocks the data write lock and result lock*/
	private void uwr() {
		dataLock.writeLock().unlock();
		resultLock.unlock();
	}

    /**
     * Check, whether a user is among registered users of this server.
     *
     * @param 	username	Username of the user to be checked
     * @return			True when the user is registered, false otherwise
     */
    public boolean isRegisteredUser(String username){
        //We just have to check the cache, because we keep it synchronized at all times
        String escapedUsername = escapeSQL(username);

		lr();
		try {
        	return userCache.containsKey(escapedUsername);
		} finally {
			ur();
		}
    }

    /**
     * Register a new user with the specified username.
     * This method should only be called after isRegisteredUser method returns false.
     *
     * @param	username	Username of the newly registered user
     * @throws  Exception       In case of DB access error
     */
    public void registerUser(String username) throws Exception{
        String escapedUsername = escapeSQL(username);

		lwr();
		try {
	        //First, insert the new user into table
	        statement.executeUpdate("INSERT INTO users (`jabber_id`) VALUES ('"+escapedUsername+"')");

	        //Then retrieve the newly added value with its id and save it into cache
	        result = resultStatement.executeQuery("SELECT jabber_id, id FROM users WHERE jabber_id = '"+escapedUsername+"'");
	        while (result.next()){
    	        userCache.put(result.getString("jabber_id"), new UserData(result.getLong("id"), 0));
	        }
		} finally {
			uwr();
		}
    }

    /**
     * Unegister a user with the specified username.
     * This method should only be called after isRegisteredUser method returns true.
     *
     * @param	username	Username of the user to be unregistered
     * @throws  Exception       In case of DB access error
     */
    public void unregisterUser(String username) throws Exception{
        String escapedUsername = escapeSQL(username);

		lw();
		try {
	        UserData ud = userCache.get(escapedUsername);

	        //Disallow deletion of administrators
	        if (adminCache.containsKey(escapedUsername))
    	        throw new Exception("Cannot unregister a user that is an administrator");

	       	//Delete the user from the table
	        statement.executeUpdate("DELETE FROM users WHERE jabber_id = '"+escapedUsername+"'");

	        //Delete the user from the cache
	        userCache.remove(escapedUsername);

	        //Delete user's planned events
	        statement.executeUpdate("DELETE FROM events WHERE user_or_group_id = '"+ud.user_id+"'");

	        //Delete user's subscription to groups
	        statement.executeUpdate("DELETE FROM groupdata WHERE jabber_id = '"+escapedUsername+"'");
		} finally {
			uw();
		}
    }

    /**
     * Returns the string of all the user ids
     *
     * @return	The string of all the user ids
     */
    public String getUsers(){
        String ret = null;
        try {
            ret = "Jabber id / difference of local time from server time / character encoding\n";

			lr();

            //Get the set of all users from the user cache
            Iterator it = userCache.entrySet().iterator();

            //Append their ids to the returned string
            while (it.hasNext()){
                Map.Entry<String, UserData> entry = (Map.Entry<String, UserData>)it.next();
                ret += unescapeSQL(entry.getKey()) + " / " + entry.getValue().gmt_offset/3600000 + ":"
                + ((entry.getValue().gmt_offset/60000%60 <= 9)?"0":"") + entry.getValue().gmt_offset/60000%60 + "\n";
            }

			ur();

        } catch (Exception e) {
            System.err.println("Map illegal state exception - this should not happen");
        }
        return ret;
    }

    /**
     * Gets the offset of the given user to the server time
     *
     * @param	username	The Jabber id of the user
     * @return			Offset from the server time in milliseconds or 0 if the user does not exist
     */
    public long getUserTimeOffset(String username){
        String escapedUsername = escapeSQL(username);

		lr();
        UserData ud = userCache.get(escapedUsername);
		ur();

        if (ud == null) return 0;
        else return ud.gmt_offset;
    }

    /**
     * Sets the time offset of a user to the given value
     *
     * @param	username	The Jabber id of the user
     * @param   newOffset       The new offset of the user from the server (in milliseconds)
     * @throws	Exception	If the username is not in the DB or if the DB cannot be updated
     */
    public void setUserTimeOffset(String username, long newOffset) throws Exception{
        String escapedUsername = escapeSQL(username);

		lw();
		try {
	        UserData ud = userCache.get(escapedUsername);

    	    if (ud == null) throw new Exception("Invalid username");

        	//Update the DB
	        statement.executeUpdate("UPDATE users SET gmt_offset="+newOffset+" WHERE id="+ud.user_id);

    	    //Update the data in cache
        	ud.gmt_offset = newOffset;
		} finally {
			uw();
		}
    }

    // Admin privileges codes:
    // modify admins - 1
    // modify users - 2
    // modify groups - 4
    // modify settings - 8

    /**
     * Check, whether the user is an admin that can modify administrators and their rights
     *
     * @param	username	The user in question
     * @return                  True if the admin can modify other admins, false otherwise
     */
    public boolean adminCanModifyAdmins(String username){
        String escapedUsername = escapeSQL(username);

		lr();
        Integer privs = adminCache.get(escapedUsername);
		ur();

        if (privs == null) return false;
        if ((privs.intValue() & 1) == 1) return true;
        else return false;
    }

    /**
     * Check, whether the user is an admin that can modify registration of users
     *
     * @param	username	The user in question
     * @return                  True if the admin can modify users, false otherwise
     */
    public boolean adminCanModifyUsers(String username){
        String escapedUsername = escapeSQL(username);

		lr();
        Integer privs = adminCache.get(escapedUsername);
		ur();

        if (privs == null) return false;
        if ((privs.intValue() & 2) == 2) return true;
        else return false;
    }

    /**
     * Check, whether the user is an admin that can modify and view groups
     *
     * @param	username	The user in question
     * @return                  True if the admin can modify groups, false otherwise
     */
    public boolean adminCanModifyGroups(String username){
        String escapedUsername = escapeSQL(username);

		lr();
        Integer privs = adminCache.get(escapedUsername);
		ur();

        if (privs == null) return false;
        if ((privs.intValue() & 4) == 4) return true;
        else return false;
    }

    /**
     * Sets the privileges for the given user.
     *
     * @return  The string with message informing about the success
     * @param   username        The JID of the user in question
     * @param   newPrivileges   The new privileges that should be set for the user
     * @throws  Exception       If the user does not exist or in case of database error or incorrect parameters
     */
    public String setAdminPrivileges(String username, int newPrivileges) throws Exception{
        String escapedUsername = escapeSQL(username);

		lwr();
		try {

        //Throw an exception if the user does not exist
        result = resultStatement.executeQuery("SELECT * FROM users WHERE jabber_id='"+escapedUsername+"'");
        if (!result.next()) throw new Exception("Could not set admin privileges - user does not exist");

        //Remove all admin privileges from the user
        if (newPrivileges == 0) {
            statement.executeUpdate("DELETE FROM admins WHERE jabber_id='"+escapedUsername+"'");
            adminCache.remove(escapedUsername);
            return "Removed all administrator privileges from user "+username;
        //Change or newly set privileges of the user
        } else if (newPrivileges > 0 && newPrivileges < 16) {
            statement.executeUpdate("DELETE FROM admins WHERE jabber_id='"+escapedUsername+"'");
            statement.executeUpdate("INSERT INTO admins (`jabber_id`,`privileges`) VALUES ('"+escapedUsername+"',"+newPrivileges+")");
            adminCache.put(escapedUsername, newPrivileges);
            return "Modified administrator privileges of user "+username;
        } else {
            throw new Exception("Could not set admin privileges - number should be between 0 and 15");
        }

		} finally {
			uwr();
		}
    }

    /**
     * Returns the string of all the admins and their privileges
     *
     * @return	The string of all the admins
     * @throws  Exception   In case of database access error
     */
    public String getAdmins() throws Exception{
        String ret = "";

		lr();
		try {

        //Get the set of all admins from the cache
        Iterator it = adminCache.entrySet().iterator();

        //Append their ids and privileges to the returned string
        while (it.hasNext()){
            Map.Entry<String, Integer> adminEntry = (Map.Entry<String, Integer>)it.next();
            ret += unescapeSQL(adminEntry.getKey()) + ", privileges: ";
            int privs = adminEntry.getValue().intValue();
            ret += privs;
            for (int i = 1; i <= 8; i <<= 1){
                if ((privs & i) == i)
                    switch (i) {
                        case 1:
                            ret += ", modify admins";
                        break;
                        case 2:
                            ret += ", modify users";
                        break;
                        case 4:
                            ret += ", modify groups";
                        break;
                        case 8:
                            ret += ", modify settings";
                        break;
                    }
            }
            ret += "\n";
        }

		} finally {
			ur();
		}

        return ret;
    }

    /**
     * Creates a group with given name and description
     * 
     * @param	name		Name of the group that will be created
     * @param	description	String with description of the new group
     * @return                  Message with information about success (if successful)
     * @throws  Exception       If the group cannot be added or the database cannot be accessed
     */
    public String addGroup(String name, String description) throws Exception{
        String escapedName = escapeSQL(name);

		lwr();
		try {

        result = resultStatement.executeQuery("SELECT id FROM groups WHERE "
                                      + "group_name ='"+escapedName+"'");
        //The group with given name already exists
        if (result.next()){
            throw new Exception("Could not create new group. Group \""+name+"\" already exists with ID "
            + result.getInt(1));
        }

        statement.executeUpdate("INSERT INTO groups (`group_name`,`description`) "
                             + "VALUES ('"+escapedName+"','"+escapeSQL(description)+"')");
		} finally {
			uwr();
		}

        return "Successfully created new group";
    }

    /**
     * Change the given group's name and/or description
     *
     * @param   groupID         ID number of the group that should be modified
     * @param   newDescription  The new description of the group (can be null to keep the old description)
     * @param   newName         The new name of the group (can be null to keep the old name)
     * @param   self_subscription   The new state for group self-subscription: 0 = don't allow; 1 = allow; other value = don't modify
     *                              value of this flag
     * @return                  String informing about success if successful
     * @throws  Exception       If the group cannot be modified or DB cannot be accessed
     */
    public String modifyGroup(long groupID, String newDescription, String newName, int self_subscription) throws Exception{
        String escapedDescription = null;
        if (newDescription != null) escapedDescription = escapeSQL(newDescription);
        String escapedName = null;
        if (newName != null) escapedName = escapeSQL(newName);

		boolean firstSet = true;
        String command = "UPDATE groups SET ";
        if (escapedName != null) {
            command += "group_name='"+escapedName+"' ";
			firstSet = false;
		}
        if (escapedDescription != null) {
			if (!firstSet) command += ", ";
            command += "description='"+escapedDescription+"' ";
			firstSet = false;
		}
        if (self_subscription == 0) {
			if (!firstSet) command += ", ";
            command += "self_subscription='0' ";
			firstSet = false;
		}
        if (self_subscription == 1) {
			if (!firstSet) command += ", ";
            command += "self_subscription='1' ";
			firstSet = false;
		}
        command += "WHERE id="+groupID;

		lw();
		try {
        statement.executeUpdate(command);
        if (statement.getUpdateCount() != 1)
            throw new Exception("Could not modify group. No group with id "+groupID+" exists.");
		} finally {
			uw();
		}

        return "Successfully modified group "+groupID;
    }

    /**
     * Remove the whole group with user subscriptions and planned events
     *
     * @param   groupID     ID number of the group to be deleted
     * @throws  Exception   In case of incorrect group ID or database error
     * @return              String with the information about the operation
     */
    public String removeGroup(long groupID) throws Exception{
		lwr();
		try {

        //First check, whether the group exists
        result = resultStatement.executeQuery("SELECT * FROM groups WHERE id="+groupID);
        if (!result.next())
            throw new Exception("Could not remove group. No group with id "+groupID+" exists.");

        //Remove group events
        statement.executeUpdate("DELETE FROM events WHERE groupevent = 1 AND user_or_group_id = '"+groupID+"'");
        //Remove group subscriptions
        statement.executeUpdate("DELETE FROM groupdata WHERE groupid = '"+groupID+"'");
        //Remove the group
        statement.executeUpdate("DELETE FROM groups WHERE id = '"+groupID+"'");

		} finally {
			uwr();
		}

        return "Successfully removed group with ID "+groupID;
    }

    /**
     * List all users subscribed to the given group
     *
     * @param   groupID     ID number of the group, whose users are to be listed
     * @throws  Exception   In case of an incorrect group ID or database error
     * @return              String with the list of all group users
     */
    public String listGroupUsers(long groupID) throws Exception{
		String ret;

		lrr();
		try {

        //First check, whether the group exists at all
        result = resultStatement.executeQuery("SELECT * FROM groups WHERE id="+groupID);
        if (!result.next())
            throw new Exception("Could not list users of group. No group with id "+groupID+" exists.");

        //List users
        result = resultStatement.executeQuery("SELECT jabber_id, privileges FROM groupdata WHERE groupid = '"+groupID+"'");
        ret = "Jabber ID / User privileges for the group (1 - modify, 2 - list, 4 - plan)\n";
        while (result.next()){
            ret += result.getString("jabber_id") + " / " + result.getString("privileges") + "\n";
        }

		} catch (Exception e) {
			ret = e.getMessage();
		} finally {
			urr();
		}

        return ret;
    }

    /**
     * Subscribes the given user to the group.
     * This user must, however, be registered with this jjsched server
     *
     * @param   groupID     ID number of the group to which the user should be subscribed
     * @param   user        Jabber ID of the user
     * @throws  Exception   If the user is not registered with the server, the group does not exist or in case of DB error
     * @return              Information string about the subscription
     */
    public String subscribeToGroup(long groupID, String user) throws Exception{
        String escapedUser = escapeSQL(user);

		lwr();
		try {

        //Check if the user is registered
        result = resultStatement.executeQuery("SELECT * FROM users WHERE jabber_id='"+escapedUser+"'");
        if (!result.next())
            throw new Exception("Could not subscribe user to group. User is not registered.");

        //Check if the group exists
        result = resultStatement.executeQuery("SELECT * FROM groups WHERE id="+groupID);
        if (!result.next())
            throw new Exception("Could not subscribe user to group. Group does not exist.");

        statement.executeUpdate("INSERT INTO groupdata (`groupid`, `jabber_id`, `privileges`) VALUES "
                + "('"+groupID+"', '"+escapedUser+"', '0')");
		} finally {
			uwr();
		}

        return "User "+user+" subscribed to group number "+groupID;
    }

    /**
     * Unsubscribes the given user from the group.
     *
     * @param   groupID     ID number of the group from which the user should be unsubscribed
     * @param   user        Jabber ID of the user
     * @throws  Exception   If the user is not subscribed to the group, if the group does not exist or in case of DB error
     * @return              Information string about the unsubscription
     */
    public String unsubscribeFromGroup(long groupID, String user) throws Exception{
        String escapedUser = escapeSQL(user);

		lwr();
		try{

        //Check if the group exists
        result = resultStatement.executeQuery("SELECT * FROM groups WHERE id="+groupID);
        if (!result.next())
            throw new Exception("Could not unsubscribe user from group. Group does not exist.");

        //Check if the user is in the group
        result = resultStatement.executeQuery("SELECT * FROM groupdata WHERE jabber_id='"+escapedUser+"'");
        if (!result.next())
            throw new Exception("Could not unsubscribe user from group. User is not subscribed.");

        statement.executeUpdate("DELETE FROM groupdata WHERE groupid="+groupID+" AND "
                + " jabber_id='"+escapedUser+"'");

		} finally {
			uwr();
		}

        return "User "+user+" unsubscribed from group number "+groupID;
    }

    /**
     * Sets the privileges of the given user for modifying the given group.
     * The privileges are a combination of 1 - user can tamper with the group,
	 * 2 - user can view group information, 4 - user can plan group events
     *
     * @param   groupID ID number of the group in question
     * @param   user    The Jabber ID of the user whose privileges should be modified
     * @param   newPrivileges   The new value of privileges
     * @return          String with information about success
     * @throws  Exception   In case of DB access error
     */
    public String setUserGroupPrivileges(long groupID, String user, int newPrivileges) throws Exception{
        String escapedUser = escapeSQL(user);

		lwr();
		try {

        //Check if the group exists
        result = resultStatement.executeQuery("SELECT * FROM groups WHERE id="+groupID);
        if (!result.next())
            throw new Exception("Could not set user privileges in the group. Group does not exist.");

        //Check if the user is in the group
        result = resultStatement.executeQuery("SELECT * FROM groupdata WHERE jabber_id='"+escapedUser+"'");
        if (!result.next())
            throw new Exception("Could not set user privileges in the group. User is not subscribed to the group.");

        //Check whether the privileges are in allowed format
        if (newPrivileges < 0 || newPrivileges > 7)
            throw new Exception("Could not set user privileges in the group. Privileges are not in allowed format.");

        statement.executeUpdate("UPDATE groupdata SET privileges='"+newPrivileges+"' WHERE groupid="+groupID+" AND "+
                                "jabber_id='"+escapedUser+"'");

		} finally {
			uwr();
		}

        return "Group privileges of user "+user+" for group number "+groupID+" have been set to "+newPrivileges;
    }

    /**
     * Queries the DB for all groups and their descriptions
     *
     * @return	The string with all groups and their descriptions
     * @throws  Exception   In case of DB access error
     */
    public String getGroups() throws Exception{
		String out;

		lrr();
		try {

        result = resultStatement.executeQuery("SELECT id, group_name, description, self_subscription FROM groups");

        out = "List of existing groups:\n";
        while (result.next()){
            out += "Group #" + result.getLong(1) + ": \"" + unescapeSQL(result.getString("group_name")) + "\"" +
                    " user self-subscription allowed: " + result.getBoolean("self_subscription") + " \n" +
                    "\t" + unescapeSQL(result.getString("description")) + "\n";
        }

		} finally {
			urr();
		}

        return out;
    }

    /**
     * Finds the first group with the given name and returns it ID number
     *
     * @param   groupname   The name of the group
     * @throws  Exception   If no group with the given name exists
     * @return              The ID number of the first grou with the given name
     */
    public long getGroupID(String groupname) throws Exception{
        String escapedName = escapeSQL(groupname);

		lrr();
		try {

        result = resultStatement.executeQuery("SELECT id FROM groups WHERE group_name='"+escapedName+"'");
        if (!result.next()) throw new Exception("Group of given name does not exist");

		} finally {
			urr();
		}

        return result.getLong("id");
    }

    /**
     * Determines whether the given user can modify the given group
     *
     * @param   user        The user whose privileges we question
     * @param   groupID     ID number of the group in question
     * @throws  Exception   If the user isn't a user from the group or the group doesn't exist or if the DB access fails
     * @return              True if the user can modify the group, false otherwise
     */
    public boolean userCanModifyGroup(String user, long groupID) throws Exception{
        String escapedUser = escapeSQL(user);

		lrr();
		try {

        result = resultStatement.executeQuery("SELECT privileges FROM groupdata WHERE jabber_id='"+escapedUser+"' AND groupid="+groupID);
        if (!result.next()) throw new Exception("User "+user+" doesn't belong to group number "+groupID+" or no such group exists.");
        if ((result.getInt("privileges") & 1) == 1)
            return true;
        else return false;

		} finally {
			urr();
		}
    }

    /**
     * Determines whether the given user can view the given group info
     *
     * @param   user        The user whose privileges we question
     * @param   groupID     ID number of the group in question
     * @throws  Exception   If the user isn't a user from the group or the group doesn't exist or if the DB access fails
     * @return              True if the user can view the group info, false otherwise
     */
    public boolean userCanListGroup(String user, long groupID) throws Exception{
        String escapedUser = escapeSQL(user);

		lrr();
		try {

        result = resultStatement.executeQuery("SELECT privileges FROM groupdata WHERE jabber_id='"+escapedUser+"' AND groupid="+groupID);
        if (!result.next()) throw new Exception("User "+user+" doesn't belong to group number "+groupID+" or no such group exists.");
        if ((result.getInt("privileges") & 2) == 2)
            return true;
        else return false;

		} finally {
			urr();
		}
    }

    /**
     * Determines whether the given user can plan events for the given group
     *
     * @param   user        The user whose privileges we question
     * @param   groupID     ID number of the group in question
     * @throws  Exception   If the user isn't a user from the group or the group doesn't exist or if the DB access fails
     * @return              True if the user can plan events for the group, false otherwise
     */
    public boolean userCanPlanGroup(String user, long groupID) throws Exception{
        String escapedUser = escapeSQL(user);

		lrr();
		try {

        result = resultStatement.executeQuery("SELECT privileges FROM groupdata WHERE jabber_id='"+escapedUser+"' AND groupid="+groupID);
        if (!result.next()) throw new Exception("User "+user+" doesn't belong to group number "+groupID+" or no such group exists.");
        if ((result.getInt("privileges") & 4) == 4)
            return true;
        else return false;

		} finally {
			urr();
		}
    }

    /**
     * Determines whether ordinary users can subscribe to the group
     *
     * @param   groupID     ID number of the group in question
     * @throws  Exception   If the group doesn't exist or in case of DB access failure
     * @return              True if users can subscribe to the group, false otherwise
    */
    public boolean usersCanSubscribe(long groupID) throws Exception{
		lrr();
		try {

        result = resultStatement.executeQuery("SELECT self_subscription FROM groups WHERE id="+groupID);
        if (!result.next()) throw new Exception("Group number "+groupID+" does not exist.");
        if (result.getBoolean("self_subscription") == true)
            return true;
        else return false;

		} finally {
			urr();
		}
    }

    /**
     * Adds an event to the event table in the DB.
     *
     * The event must be in the future of the current server time. This method serves for planning both user and group events.
     * Whether the event is a user or group event is determined in the PlanStruct.singleuser atribute
     *
     * @param	struc	    The structure, containing information about the plan to be added
     * @throws  Exception   In case of invalid user or group name or in case of DB access error
     */
    public void addEvent(PlanStruct struc) throws Exception{
        //Check if the timestamp is in future by subtracting current time and checking against
        //an arbitrary number of seconds: 10
        if (struc.timestamp - System.currentTimeMillis() < -10000) throw new Exception ("Cannot add a planned event - event lies in past");

        //In every case, we need to escape user input
        struc.username = escapeSQL(struc.username);
        struc.description = escapeSQL(struc.description);

		lwr();
		try {

        //The event should be planned for a single user
        if (struc.singleuser) {
            //Check if the user exists
            UserData user = userCache.get(struc.username);
            if (user == null) throw new Exception("Cannot add a planned event - invalid username: "+struc.username);

            statement.executeUpdate("INSERT INTO events (`time`,`user_or_group_id`,`description`,`groupevent`) "
                                  + "VALUES ('"+timeHandler.getSqlFormattedTime(struc.timestamp)
                                  + "',"+user.user_id+",'"+struc.description+"','0')");
        //The event is for a whole group
        } else {
            long id;

            if (struc.groupId == 0) {
                //Check whether the group exists and get the group id
                result = resultStatement.executeQuery("SELECT id FROM groups WHERE group_name = '"+struc.username+"'");
                if (!result.next()) throw new Exception("Cannot add a planned event - invalid group name: "+struc.username);
                id = result.getLong("id");
            } else id = struc.groupId;

                //Insert the new group event
                statement.executeUpdate("INSERT INTO events (`time`,`user_or_group_id`,`description`,`groupevent`) "
                                      + "VALUES ('"+timeHandler.getSqlFormattedTime(struc.timestamp)
                                      + "',"+id+",'"+struc.description+"','1')");
        }

		} finally {
			uwr();
		}
    }

	/**
	 * Unocks the result statement lock.
	 *
	 * This indicates that the statement can be reused because this thread stopped using the previous ResultSet.
	 */
	public void dropResults() {
		resultLock.unlock();
	}

    /**
     * Returns the result set of all user events older than the given timestamp.
     *
     * @param   timestamp   Timestamp as given by System.currentTimeMillis()
     * @return              Result set of all the desired events
     * @throws  Exception   In case of DB access error
     */
    public ResultSet getUserEventsOlderThan(long timestamp) throws Exception{
        String time = timeHandler.getSqlFormattedTime(timestamp);

		lrr();
		try {

        result = resultStatement.executeQuery("SELECT ev.description, us.jabber_id FROM events AS ev INNER JOIN users AS us "+
                                        "ON ev.user_or_group_id = us.id "+
                                        "WHERE groupevent = 0 AND time < "+time);

		} finally {
			ur(); //This is not a mistake. resultLock should be locked outside when the result set is not used anymore
		}

        return result;
    }

	/**
 	 * Returns a string with a list of all events of a given user.
 	 * 
	 * @param	userId	Jabber ID of the user whose events should be listed
	 * @return		List of all the user's events
	 */
	public String listUserEvents(String userId) throws Exception{
		lrr();
		try {
			String ret = "";
			result = resultStatement.executeQuery("SELECT * FROM events AS ev INNER JOIN users AS us "+
								"ON ev.user_or_group_id = us.id "+
								"WHERE groupevent = 0 AND jabber_id = '"+userId+"' ORDER BY time ASC");

			while (result.next()) {
				ret += timeHandler.getFormattedTime(result.getTimestamp("time").getTime());
				ret += " / ";
				ret += result.getString("description");
				ret += "\n";
			}
			return ret;
		} catch (Exception e) {
			return e.getMessage();
		} finally {
			urr();
		}
	}

	/**
 	 * Returns a string with a list of all events of a given group.
 	 * 
	 * @param	groupId	Group number of the group whose events should be listed
	 * @return		List of all the group's events
	 */
	public String listGroupEvents(long groupId) throws Exception{
		lrr();
		try {
			String ret = "";
			result = resultStatement.executeQuery("SELECT * FROM events WHERE groupevent = 1 AND user_or_group_id = "+groupId+" ORDER BY time ASC");

			while (result.next()) {
				ret += timeHandler.getFormattedTime(result.getTimestamp("time").getTime());
				ret += " / ";
				ret += result.getString("description");
				ret += "\n";
			}
			return ret;
		} catch (Exception e) {
			return e.getMessage();
		} finally {
			urr();
		}
	}

    /**
     * Deletes user events older than timestamp from the database
     *
     * @param   timestamp   Timestamp as given by System.currentTimeMillis()
     * @throws  Exception   In case of DB access error
     */
    public void deleteUserEventsOlderThan(long timestamp) throws Exception{
        String time = timeHandler.getSqlFormattedTime(timestamp);

		lw();
		try {

        statement.executeUpdate("DELETE FROM events WHERE groupevent = 0 AND time < "+time);

		} finally {
			uw();
		}
    }

    /**
     * Returns the result set of all group events older than the given timestamp.
     *
     * @param   timestamp   Timestamp as given by System.currentTimeMillis()
     * @return              Result set of all the desired events
     * @throws  Exception   In case of DB access error
     */
    public ResultSet getGroupEventsOlderThan(long timestamp) throws Exception{
        String time = timeHandler.getSqlFormattedTime(timestamp);

		lrr();
		try {

        result = resultStatement.executeQuery("SELECT ev.description, us.jabber_id FROM events AS ev INNER JOIN groupdata as gd "+
                                        "ON ev.user_or_group_id = gd.groupid INNER JOIN users AS us "+
                                        "ON gd.jabber_id = us.jabber_id "+
                                        "WHERE groupevent = 1 AND time < "+time);
		} finally {
			ur(); //This is not a mistake. resultLock should be unlocked outside when the result set is not used anymore
		}

        return result;
    }

    /**
     * Deletes group events older than timestamp from the database
     *
     * @param   timestamp   Timestamp as given by System.currentTimeMillis()
     * @throws  Exception   In case of DB access error
     */
    public void deleteGroupEventsOlderThan(long timestamp) throws Exception{
        String time = timeHandler.getSqlFormattedTime(timestamp);

		lw();
		try {
	        statement.executeUpdate("DELETE FROM events WHERE groupevent = 1 AND time < "+time);
		} finally {
			uw();
		}
    }

    /**
     * Check, whether the DB has the right structure.
     * @throws Exception In case of DB access failure
     */
    private void checkDB() throws Exception{
		lrr();
		try {

        DatabaseMetaData dbmd = conn.getMetaData();
        String confSchema = conf.getString("MySQLScheme");

        //Find out, whether the schema contains all the needed Tables
        String[] tables = {"users", "events", "groups", "groupdata", "admins"};
        for (String tableName: tables) {
            result = dbmd.getTables(null, confSchema, tableName, null);
            if (!result.next())
                throw new Exception("DB inconsistency error: Scheme "+confSchema+" does not contain table \""+tableName+"\"");
        }

        String[] columns = null;
        int[] columnTypes = null;
        String tableName = null;

        //Check for the correct form of table 'users'
        tableName = "users";
        columns = new String[]  {"jabber_id",   "id",          "gmt_offset"};
        columnTypes = new int[] {Types.VARCHAR, Types.INTEGER, Types.INTEGER};
        for (int i = 0; i < columns.length; ++i){
            result = dbmd.getColumns(null, confSchema, tableName, columns[i]);
            if (!result.next())
                throw new Exception("DB inconsistency error: Table \""+tableName+
                                    "\" does not have a column named \""+columns[i]+"\"");
            if (result.getInt("DATA_TYPE") != columnTypes[i])
                throw new Exception("DB inconsistency error: Column \""+columns[i]+
                                    "\" in table \""+tableName+"\" does not match spec.");
        }
        result = dbmd.getColumns(null, confSchema, tableName, null);
        while (result.next())
            if (!Arrays.asList(columns).contains(result.getString("COLUMN_NAME")))
                throw new Exception("DB inconsistency error: Table \""+tableName+
                                    "\" has a column (\""+result.getString("COLUMN_NAME")+"\") that is not in spec.");

        //Check for the correct form of table 'events'
        tableName = "events";
        columns = new String[]  {"id",          "groupevent",  "user_or_group_id", "time",          "description" };
        columnTypes = new int[] {Types.INTEGER, Types.TINYINT, Types.INTEGER,      Types.TIMESTAMP, Types.VARCHAR   };
        for (int i = 0; i < columns.length; ++i){
            result = dbmd.getColumns(null, confSchema, tableName, columns[i]);
            if (!result.next())
                throw new Exception("DB inconsistency error: Table \""+tableName+
                        "\" does not have a column named \""+columns[i]+"\"");
            if (result.getInt("DATA_TYPE") != columnTypes[i])
                throw new Exception("DB inconsistency error: Column \""+columns[i]+
                        "\" in table \""+tableName+"\" does not match spec.");
        }
        result = dbmd.getColumns(null, confSchema, tableName, null);
        while (result.next())
            if (!Arrays.asList(columns).contains(result.getString("COLUMN_NAME")))
                throw new Exception("DB inconsistency error: Table \""+tableName+
                        "\" has a column (\""+result.getString("COLUMN_NAME")+"\") that is not in spec.");

        //Check for the correct form of table 'groups'
        tableName = "groups";
        columns = new String[]  {"id",          "group_name",   "description", "self_subscription" };
        columnTypes = new int[] {Types.INTEGER, Types.VARCHAR,  Types.VARCHAR, Types.TINYINT   };
        for (int i = 0; i < columns.length; ++i){
            result = dbmd.getColumns(null, confSchema, tableName, columns[i]);
            if (!result.next())
                throw new Exception("DB inconsistency error: Table \""+tableName+
                        "\" does not have a column named \""+columns[i]+"\"");
            if (result.getInt("DATA_TYPE") != columnTypes[i])
                throw new Exception("DB inconsistency error: Column \""+columns[i]+
                        "\" in table \""+tableName+"\" does not match spec.");
        }
        result = dbmd.getColumns(null, confSchema, tableName, null);
        while (result.next())
            if (!Arrays.asList(columns).contains(result.getString("COLUMN_NAME")))
                throw new Exception("DB inconsistency error: Table \""+tableName+
                        "\" has a column (\""+result.getString("COLUMN_NAME")+"\") that is not in spec.");

        //Check for the correct form of table 'groupdata'
        tableName = "groupdata";
        columns = new String[]  {"recordid",  "groupid",   "jabber_id", "privileges" };
        columnTypes = new int[] {Types.INTEGER, Types.INTEGER, Types.VARCHAR, Types.TINYINT };
        for (int i = 0; i < columns.length; ++i){
            result = dbmd.getColumns(null, confSchema, tableName, columns[i]);
            if (!result.next())
                throw new Exception("DB inconsistency error: Table \""+tableName+
                        "\" does not have a column named \""+columns[i]+"\"");
            if (result.getInt("DATA_TYPE") != columnTypes[i])
                throw new Exception("DB inconsistency error: Column \""+columns[i]+
                        "\" in table \""+tableName+"\" does not match spec.");
        }
        result = dbmd.getColumns(null, confSchema, tableName, null);
        while (result.next())
            if (!Arrays.asList(columns).contains(result.getString("COLUMN_NAME")))
                throw new Exception("DB inconsistency error: Table \""+tableName+
                        "\" has a column (\""+result.getString("COLUMN_NAME")+"\") that is not in spec.");

        //Check for the correct form of table 'admins'
        tableName = "admins";
        columns = new String[]  {"jabber_id",   "privileges" };
        columnTypes = new int[] {Types.VARCHAR, Types.TINYINT};
        for (int i = 0; i < columns.length; ++i){
            result = dbmd.getColumns(null, confSchema, tableName, columns[i]);
            if (!result.next())
                throw new Exception("DB inconsistency error: Table \""+tableName+
                        "\" does not have a column named \""+columns[i]+"\"");
            if (result.getInt("DATA_TYPE") != columnTypes[i])
                throw new Exception("DB inconsistency error: Column \""+columns[i]+
                        "\" in table \""+tableName+"\" does not match spec.");
        }
        result = dbmd.getColumns(null, confSchema, tableName, null);
        while (result.next())
            if (!Arrays.asList(columns).contains(result.getString("COLUMN_NAME")))
                throw new Exception("DB inconsistency error: Table \""+tableName+
                        "\" has a column (\""+result.getString("COLUMN_NAME")+"\") that is not in spec.");

		} finally {
			urr();
		}
    }

    /**
     *  Creates the needed tables in the database and schema that were specified in the config file.
     *  This function also erases the old tables, if they are in the way.
     *
     * @throws  Exception   In case of DB access error
     */
    public void createTables() throws Exception{
		lw();
		try {

        DatabaseMetaData dbmd = conn.getMetaData();
        String confSchema = conf.getString("MySQLScheme");

        //Erase the old tables, if they exist
        String[] tables = {"users", "events", "groups", "groupdata", "admins"};
        for (String tableName: tables) {
            statement.executeUpdate("DROP TABLE IF EXISTS "+tableName);
        }

        //Create the new 'users' table
        statement.executeUpdate("CREATE TABLE users ("+
                                    "jabber_id VARCHAR(128) CHARSET utf8 NOT NULL UNIQUE KEY,"+
                                    "id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,"+
                                    "gmt_offset INTEGER NOT NULL DEFAULT 0)");

        //Create the new 'events' table
        statement.executeUpdate("CREATE TABLE events ("+
                                    "id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,"+
                                    "groupevent TINYINT UNSIGNED NOT NULL,"+
                                    "user_or_group_id INTEGER UNSIGNED NOT NULL,"+
                                    "time TIMESTAMP NOT NULL,"+
                                    "description VARCHAR(128) CHARSET utf8 NOT NULL DEFAULT '')");

        //Create the new 'groups' table
        statement.executeUpdate("CREATE TABLE groups ("+
                                    "id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,"+
                                    "group_name VARCHAR(128) charset utf8 NOT NULL DEFAULT '',"+
                                    "description VARCHAR(128) charset utf8 NOT NULL DEFAULT '',"+
                                    "self_subscription TINYINT NOT NULL DEFAULT 1)");

        //Create the new 'groupdata' table
        statement.executeUpdate("CREATE TABLE groupdata ("+
                                    "recordid INTEGER UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,"+
                                    "groupid INTEGER UNSIGNED NOT NULL,"+
                                    "jabber_id VARCHAR(128) CHARSET utf8 NOT NULL,"+
                                    "privileges TINYINT UNSIGNED NOT NULL DEFAULT 0)");

        //Create the new 'admins' table
        statement.executeUpdate("CREATE TABLE admins ("+
                                    "jabber_id VARCHAR(128) CHARSET utf8 NOT NULL PRIMARY KEY,"+
                                    "privileges TINYINT UNSIGNED NOT NULL DEFAULT 0)");

		//Register the console admin
		registerUser("console");
		setAdminPrivileges("console", 15);

        //Register the initial admin and set his privileges
        String initAdmin = conf.getString("InitialAdmin");
        initAdmin = StringParser.escapeJID(initAdmin);
        if (initAdmin == null) initAdmin = "user@host.com";
        registerUser(initAdmin);
        setAdminPrivileges(initAdmin, 15);

		} finally {
			uw();
		}
    }

    /**
     * Load users and admins from the DB into cache.
     *
     * @throws Exception In case of DB access error
     */
    private void mirrorCaches() throws Exception{
		lwr();
		try {

        result = resultStatement.executeQuery("SELECT id, jabber_id, gmt_offset FROM users");

        while (result.next()){
            userCache.put(result.getString("jabber_id"),
                          new UserData(result.getInt("id"), result.getInt("gmt_offset")));
        }

        result = resultStatement.executeQuery("SELECT jabber_id, privileges FROM admins");

        while (result.next()){
            adminCache.put(result.getString("jabber_id"), new Integer(result.getInt("privileges")));
        }

		} finally {
			uwr();
		}
    }

    /**
     * Escapes potentially harmful characters in the string (' and \ characters)
     *
     * @param	input	The string that should be escaped
     * @return		The output string
     */
    private String escapeSQL(String input){
        String output = "";
        try {
        for (int i = 0; i < input.length(); ++i){
            if (input.charAt(i)== '\'') {
                output += "\\\'";
            } else if (input.charAt(i) == '\\') {
                output += "\\\\";
            } else output += input.charAt(i);
        }
        } catch (Exception e) {}
        return output;
    }

    /**
     * Unescapes strings from the DB to their original form
     *
     * @param	input	The string to be unescaped
     * @return		The output string
     */
    private String unescapeSQL(String input){
        String output = "";
        boolean escape = false;
        try {
        for (int i = 0; i < input.length(); ++i){
            if (input.charAt(i)== '\\' && !escape) {
                escape = true;
            } else {
                escape = false;
                output += input.charAt(i);
            }
        }
        } catch (Exception e) {}
        return output;
    }

    /** The connection to the DB */
    Connection conn;
    /** Statement instance to avoid reallocating for each DB access */
    Statement statement;
    /** Second statement instance for methods that return ResultSets */
    Statement resultStatement;
    /** Temporary result set store */
    ResultSet result;
    /** Reference to the time handler class instance */
    TimeHandler timeHandler;
	/** Lock for the DB (used instead of transactions) */
	ReentrantReadWriteLock dataLock = new ReentrantReadWriteLock();
	/** Lock for result-set-returning statement (to ensure that it is not
		called before the previous ResultSet is not used anymore) */
	ReentrantLock resultLock = new ReentrantLock();

    /** Cache of subscribed users, for quicker access. **/
    HashMap<String, UserData> userCache;
    /** Cache of server admins, for quicker access. **/
    HashMap<String, Integer> adminCache;
    /** Configuration options */
    Config conf;
}

/** This class holds the cached data for the JJSched users */
class UserData{
    UserData(long newId, long newOffset){
        user_id = newId;
        gmt_offset = newOffset;
    }

    long user_id;
    long gmt_offset;
}
